<?php
// +----------------------------------------------------------------------
// | Author: July
// +----------------------------------------------------------------------
// | 创建时间: 2023-12-27 20:39:42
// +----------------------------------------------------------------------
// | 数据库管理控制器
// +----------------------------------------------------------------------

namespace july\julyadmin\admin\controller;

use july\julyadmin\builder\traits\HasBuilder;
use july\julyadmin\common\model;
use july\julyadmin\common\model\DatabaseManage as DatabaseManageModel;
use think\facade\Db;

class DatabaseManage extends Common
{
    use HasBuilder;

    //不需要登录验证的控制器和方法，如：['index']
    protected $noNeedLogin = [];
    //不需要权限验证的控制器和方法，如：['index']
    protected $noPermissionAuth = ['tableFieldsSelectPage'];

    public function __construct()
    {
        //重新初始化父类
        parent::initialize();
        //数据模型
        $this->dataModel = new DatabaseManageModel();
        //join关联查询的表信息如，['category'=>'inner','comment'=>'left']
        $this->relationWithJoins = [];
        //with预查询关联表信息，如category,comment
        $this->relationWiths = [];
        //页面标题
        $this->pageTitle = '数据库管理';
        //允许表格行内编辑的字段,如name,sex
        $this->allowRowEditField = [];
        //不允许删除的主键数组，如1,2,3
        $this->noDeletePrimarykeys = [];
        //不允许编辑的主键数组，如1,2,3
        $this->noEditPrimarykeys = [];
        //不允许禁用的主键数组，如1,2,3
        $this->noDisabledPrimarykeys = [];
        //下拉join关联查询的表信息如，['category'=>'inner','comment'=>'left']
        $this->selectRelationWithJoins = [];
        //下拉with预查询关联表信息，如category,comment
        $this->selectRelationWiths = [];
        //下拉列表默认查询条件，如 [['disabled', '=', 1]]，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.
        $this->selectWhere = [];
        //下拉列表模糊查询字段,如 'name|title'，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin，当前表可以不加
        $this->selectSearch = '';
        //下拉列表默认 Text 字段 如 '{id}#{nickname}'或者'id:{id}-名称:{name}'，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin，当前表可以不加
        $this->selectTextField = '';
        //下拉默认id字段,如：id
        $this->selectIdField = 'id';
        //下拉列表查询时的字段，如：id,name，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.，当前表可以不加
        $this->selectFields = '';
        //下拉列表父id字段，如：parent_id，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.
        $this->selectParentId = 'parent_id';
        //下拉列表查询第一层数据列表条件，如[['parent_id','=',0]],当表格为treeTable的时候，意味着你要输出的数据也会是树形的，这个时候首次查询最父层的数据列表可能需要传入这个参数，如果有配置了$this->SelectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.
        $this->selectOneParentWhere = [];
        //下拉列表查询排序，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.
        $this->selectOrder = '';
        //列表排序，如果有配置了$this->relationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.
        $this->sort = 'id desc';
        //父id字段名称，如parent_id,当表格为treeTable的时候，意味着你要输出的数据也会是树形的，这个时候需要指定父级字段名称，根据实际需求修改
        $this->parentField = '';
        //查询第一层数据列表条件，如[['parent_id','=',0]],当表格为treeTable的时候，意味着你要输出的数据也会是树形的，这个时候首次查询最父层的数据列表可能需要传入这个参数，如果有配置了$this->relationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如admin.
        $this->oneParentWhere = [];
        //表格类型,table,treeTable
        $this->tableType = 'table';
        //树形表格分组字段，比如name，那么展开和搜索箭头就会在name这一列显示，当表格为treeTable的时候会用到
        $this->treeTableGroupField = '';
        //需要合计的字段，如['显示在哪个字段','显示哪个字段的值']，如果有配置了$this->selectRelationWithJoins，这里就需要注意了，排序字段需要带上表名加一个点，如['id'=>'admin.sex','money'=>admin.money']
        $this->totalRow = [];
        //设置主键
        $this->pk = 'id';
    }

    /**
     * 设置当前页面
     * @Author   July
     * @DateTime 2024-01-13T19:41:56+0800
     * @param    $table 表格
     */
    protected function buildPage(&$table = [])
    {
        //搜索
        $table->getSearch()
            ->show('text', 'keyword', '表名/备注')->placeholder('表名/备注');

        //表格工具栏
        $updateTablesUrl = url('DatabaseManage/updateTables');
        $clearDataUrl    = url('DatabaseManage/clearData');
        $script          = <<<EOT

            <script>
                //更新数据表到数据库
                function updateTables(){
                    window.loading  = layer.load(2, { shade:  [0.2, '#000'] });
                    $.ajax({
                        type:'POST',
                        url:'{$updateTablesUrl}',
                        data:{},
                        dataType:'json',
                        success:function(res) {
                            layer.close(window.loading);
                            if(res.code==1){
                                layer.msg(res.msg,{
                                    icon:6,
                                    time:2000,
                                    shade:[0.6,'#000',false]
                                });
                                tableRefresh();
                            }else{
                                layer.msg(res.hasOwnProperty('msg') && res.msg?res.msg:'操作失败！',{
                                    icon:5,
                                    time:2000,
                                    shade:[0.6,'#000',false]
                                });
                            }
                            $(window).resize();
                        },
                        error:function(res){
                            $(window).resize();
                            layer.close(window.loading);
                            layer.msg(JSON.stringify(res),{
                                icon:5,
                                time:2000,
                                shade:[0.6,'#000',false]
                            });
                        }
                    });
                }
                //清理索引和碎片
                function clearData(){
                    window.loading  = layer.load(2, { shade:  [0.2, '#000'] });
                    $.ajax({
                        type:'POST',
                        url:'{$clearDataUrl}',
                        data:{},
                        dataType:'json',
                        success:function(res) {
                            layer.close(window.loading);
                            if(res.code==1){
                                layer.msg(res.msg,{
                                    icon:6,
                                    time:2000,
                                    shade:[0.6,'#000',false]
                                });
                                tableRefresh();
                            }else{
                                layer.msg(res.hasOwnProperty('msg') && res.msg?res.msg:'操作失败！',{
                                    icon:5,
                                    time:2000,
                                    shade:[0.6,'#000',false]
                                });
                            }
                            $(window).resize();
                        },
                        error:function(res){
                            $(window).resize();
                            layer.close(window.loading);
                            layer.msg(JSON.stringify(res),{
                                icon:5,
                                time:2000,
                                shade:[0.6,'#000',false]
                            });
                        }
                    });
                }
            </script>

EOT;
        $table->getToolbar()
            ->addBtn(true, null, '98%', '98%')
            ->linkBtn('updateTables', '更新数据表', 'javascript:updateTables()', 'mdi mdi-clipboard-text-search', 'btn btn-pink')
            ->linkBtn('clearData', '清理索引/碎片', 'javascript:clearData()', 'mdi mdi-archive-refresh-outline', 'btn btn-secondary')
            ->addScript($script)
            ->refreshBtn();

        //表格数据
        $table->showCheckbox(false);
        $table->show('id', 'ID')->width(80)->sort(true);
        $table->show('name', '表名')->width(300)->sort(true);
        $table->show('engine', '引擎')->width(90)->sort(true);
        $table->show('charset', '编码')->width(80)->sort(true);
        $table->show('rows', '内容行数')->width(110)->sort(true);
        $table->show('data_length', '数据量(字节)')->width(140)->sort(true);
        $table->show('index_length', '索引大小(字节)')->width(140)->sort(true);
        $table->show('data_free', '碎片大小(字节)')->width(140)->sort(true);
        $table->show('comment', '备注');

        //表格右侧操作列
        $table->showRowHandleMenus()->width(190);
        $table->getTableRowBtns()
            ->editBtn(true, null, '98%', '98%')
            ->viewBtn(true, null, '98%', '98%')
            ->openWindowBtn('tableIndex', '索引', url('DatabaseManage/tableIndex'))
            ->br()
            ->openWindowBtn('modelRelation', '模型关联', url('DatabaseManage/modelRelation'))
            ->openWindowBtn('createModel', '生成模型', url('DatabaseManage/createModel'))
            ->deleteBtn();

    }

    /**
     * 处理数据
     * 因为查询出来的数据可能需要二次处理
     * @Author   July
     * @DateTime 2024-01-13T19:44:46+0800
     * @param  array   &$data 查询出来的数据
     */
    protected function handleData(&$data = [])
    {

    }

    /**
     * 处理查询条件
     * @Author   July
     * @DateTime 2023-11-16T20:45:54+0800
     * @param array $search  查询条件
     * @return  array $where
     */
    protected function filterWhere($search = [])
    {
        $where = [];
        foreach ($search as $k => $v) {
            if ($v === "") {
                continue;
            }
            switch ($k) {
                case "keyword":
                    $where[] = ['name|comment', 'like', '%' . $v . '%'];
                    break;
            }
        }

        return $where;
    }

    /**
     * 处理下拉列表查询条件
     * @Author   July
     * @DateTime 2024-01-13T19:47:05+0800
     * @param  array $where  现有的查询条件
     * @return array
     */
    protected function filterSelectPageWhere($where = [])
    {

        return $where;
    }

    /**
     * 处理合计行信息
     * @Author   July
     * @DateTime 2023-11-16T20:45:54+0800
     * @param    array                   $where    已经处理过的最终查询条件，可以自己覆盖，用来自己重新查询数据
     * @param    integer                 $limit    每页多少条
     * @param    string                  $sort     排序
     * @param    integer                 $page     第几页
     * @param    array                   $totalRow 已经查询出来的合计信息，可以自己覆盖返回
     * @return   array                   $totalRow 将处理过的最终信息返回
     */
    protected function filterTotalRow($where = [], $limit = 20, $sort = '', $page = 1, $totalRow = [])
    {
        return $totalRow;
    }

    /**
     * @Author   July
     * @DateTime 2024-01-13T19:49:03+0800
     * @param    Object                   &$form  form对象
     * @param    integer                  $isEdit 是否是编辑：0否，1是
     * @param    array                    &$data  当前这条数据的内容
     */
    protected function buildForm(&$form, $isEdit, &$data = [])
    {
        //设置表单整体宽度为最大12
        $form->setColWidth(12);
        //设置标题宽度为2，设置表单元素宽度为10
        $form->size(2, 10);

        //查询支持的编码
        $charsets = [];
        foreach (Db::query('SHOW CHARSET') as $k => $v) {
            $charsets[$v['Charset']] = $v['Charset'];
        }
        //查询支持的字符字符集
        $collations = [];
        foreach (Db::query('SHOW COLLATION') as $k => $v) {
            $collations[$v['Collation']] = $v['Collation'];
        }
        //查询支持的引擎列表
        $engines = [];
        foreach (Db::query('SHOW ENGINES') as $k => $v) {
            $engines[$v['Engine']] = $v['Engine'];
        }

        $form->show('text', 'name', '表名')->readonly($isEdit)->required();
        $form->show('select', 'engine', '数据表引擎')->options($engines)->default('InnoDB')->required();
        $form->show('select', 'charset', '编码')->options($charsets)->default('utf8mb4')->required();
        $form->show('select', 'collation', '排序规则')->options($collations)->default('utf8mb4_general_ci')->required();
        $form->show('textarea', 'comment', '表注释');

        $items = [];

        if ($isEdit) {
            $database = Config("database.connections.mysql.database");
            $fields   = Db::query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{$database}' AND TABLE_NAME='{$data['name']}' ORDER BY ORDINAL_POSITION ASC");
            foreach ($fields as $k => $v) {
                if ($v['COLUMN_DEFAULT'] === null) {
                    $v['COLUMN_DEFAULT'] = '';
                }
                $length = '';
                $preg   = "/([a-zA-Z_\-0-9]+)\(([\d|\,]+)\)/";
                preg_match_all($preg, $v['COLUMN_TYPE'], $column_arr);
                $decimal_arr = isset($column_arr[2][0]) ? explode(',', $column_arr[2][0]) : [];
                $items[]     = [
                    'original_field' => $v['COLUMN_NAME'],
                    'field'          => $v['COLUMN_NAME'],
                    'type'           => isset($column_arr[1][0]) ? $column_arr[1][0] : $v['COLUMN_TYPE'],
                    'length'         => isset($decimal_arr[0]) ? $decimal_arr[0] : '',
                    'decimal'        => isset($decimal_arr[1]) ? $decimal_arr[1] : '',
                    'default'        => $v['COLUMN_DEFAULT'],
                    'notnull'        => $v['IS_NULLABLE'] == 'NO' ? 1 : 0,
                    'key'            => $v['COLUMN_KEY'] == 'PRI' && $v['IS_NULLABLE'] == 'NO',
                    'auto_increment' => $v['EXTRA'] == 'auto_increment' ? 1 : 0,
                    'comment'        => $v['COLUMN_COMMENT'],
                    'sort'           => $k,
                ];
            }
            $data['fields'] = $items;
        }
        $default_items = [];
        if (!$isEdit) {
            $default_fields = [
                ['field' => 'create_time', 'type' => 'datetime', 'comment' => '创建时间', 'sort' => 98],
                ['field' => 'update_time', 'type' => 'datetime', 'comment' => '更新时间', 'sort' => 99],
                ['field' => 'delete_time', 'type' => 'datetime', 'comment' => '删除时间', 'sort' => 100],
            ];
            foreach ($default_fields as $dfk => $dfv) {
                $default_items[] = [
                    'field'   => $dfv['field'],
                    'type'    => $dfv['type'],
                    'default' => '',
                    'key'     => 'NO',
                    'comment' => $dfv['comment'],
                    'sort'    => $dfv['sort'],
                ];
            }
        }

        $types = DatabaseManageModel::TYPES;
        $form->show('items', 'fields', '字段')->default($default_items)->showRowHandleMenus(true)->addBtn(true)->deleteBtn(true)->style('min-width:1400px;')
            ->with(function ($form) use ($types) {
                $form->show('hidden', 'original_field', '原字段');
                $form->show('text', 'field', '字段名')->itemsTitleStyle('width:200px;text-align:center;')->required();
                $form->show('select', 'type', '类型')->itemsTitleStyle('width:200px;text-align:center;')->options($types)->required();
                $form->show('number', 'length', '长度')->itemsTitleStyle('width:200px;text-align:center;');
                $form->show('number', 'decimal', '小数')->itemsTitleStyle('width:200px;text-align:center;');
                $form->show('text', 'default', '默认值')->itemsTitleStyle('width:200px;text-align:center;');
                $form->show('checkbox', 'notnull', '不是 NULL')->itemsTitleStyle('width:30px;text-align:center;')->options([1 => '']);
                $form->show('checkbox', 'key', '键')->itemsTitleStyle('width:20px;text-align:center;')->options([1 => '']);
                $form->show('checkbox', 'auto_increment', '自增')->itemsTitleStyle('width:20px;text-align:center;')->options([1 => '']);
                $form->show('number', 'sort', '排序')->itemsTitleStyle('width:150px;text-align:center;');
                $form->show('textarea', 'comment', '注释')->itemsTitleStyle('width:400px;text-align:center;');
            })->required();
    }

    /**
     * 设置索引
     * @Author   July
     * @DateTime 2024-01-13T19:58:26+0800
     * @return   [type]                   [description]
     */
    public function tableIndex()
    {
        $pk = input("get." . $this->getPk());
        if (request()->isPost()) {
            $indexs         = input('post.indexs/a', []);
            $tableName      = $this->dataModel->where([$this->getPk() => $pk])->value('name');
            $database       = Config("database.connections.mysql.database");
            $originalIndexs = Db::query("SHOW INDEX FROM `{$tableName}`");
            if (!$originalIndexs && !$indexs) {
                $this->error('索引不能为空');
            }
            //删除索引
            $originalIndexs = Db::query("SHOW INDEX FROM `{$tableName}`");
            foreach ($originalIndexs as $ik => $iv) {
                if ($ik > 0) {
                    $sql = "ALTER TABLE `{$database}`.`{$tableName}` DROP INDEX `{$iv['Key_name']}`";
                    Db::execute($sql);
                }
            }

            //添加索引
            foreach ($indexs as $k => $v) {
                try {
                    $info = [
                        'name|索引名'    => 'require',
                        'field|关联字段'  => 'require',
                        'type|索引类型'   => 'require',
                        'action|索引方法' => 'require',
                    ];
                    validate($info)->check($v);
                } catch (\think\exception\ValidateException $e) {
                    $this->error($e->getError());
                }

                if ($v['type'] == 'NORMAL') {
                    $v['type'] = 'INDEX';
                }
                $sql = "ALTER TABLE `{$database}`.`{$tableName}` ADD {$v['type']} `{$v['name']}`(`{$v['field']}`) USING {$v['action']} COMMENT '{$v['comment']}'";
                Db::execute($sql);
            }
            return json(['code' => 1, 'msg' => '保存成功！']);
        } else {
            $builder = $this->builder();
            $form    = new \july\julyadmin\builder\Form($builder, '索引');
            //设置表单整体宽度为最大12
            $form->setColWidth(12);
            //设置标题宽度为2，设置表单元素宽度为10
            $form->size(2, 10);

            $form->pk = $this->getPk();
            //索引类型
            $indexTypes = ['FULLTEXT' => 'FULLTEXT', 'NORMAL' => 'NORMAL', 'SPATIAL' => 'SPATIAL', 'UNIQUE' => 'UNIQUE'];
            //索引方法
            $indexAction = ['BTREE' => 'BTREE', 'HASH' => 'HASH'];
            //当前表字段列表
            $fields    = [];
            $tableName = $this->dataModel->where([$this->getPk() => $pk])->value('name');
            foreach (Db::query("SELECT COLUMN_NAME,COLUMN_COMMENT FROM information_schema.columns WHERE table_name='{$tableName}'") as $k => $v) {
                $fields[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }

            $originalIndexs = Db::query("SHOW INDEX FROM `{$tableName}`");
            $items          = [];

            foreach ($originalIndexs as $k => $v) {
                if ($k == 0) {
                    continue;
                }
                if ($v['Non_unique'] == 0 && $v['Index_type'] == 'BTREE') {
                    $type = 'UNIQUE';
                } else if ($v['Non_unique'] == 1 && $v['Index_type'] == 'BTREE') {
                    $type = 'NORMAL';
                } else {
                    $type = $v['Index_type'];
                }
                if ($v['Index_type'] == 'HASH') {
                    $action = 'HASH';
                } else {
                    $action = 'BTREE';
                }
                $items[] = [
                    'name'    => $v['Key_name'],
                    'field'   => $v['Column_name'],
                    'type'    => $type,
                    'action'  => $action,
                    'comment' => $v['Index_comment'],
                ];
            }
            $form->data = ['indexs' => $items];
            $form->show('items', 'indexs', '索引')->showRowHandleMenus(true)->addBtn(true)->deleteBtn(true)->style('min-width:1100px;')
                ->with(function ($form) use ($indexTypes, $indexAction, $fields) {
                    $form->show('text', 'name', '索引名')->itemsTitleStyle('width:200px;text-align:center;')->required();
                    $form->show('select', 'field', '关联字段')->itemsTitleStyle('width:200px;text-align:center;')->options($fields)->required();
                    $form->show('select', 'type', '索引类型')->default('NORMAL')->itemsTitleStyle('width:200px;text-align:center;')->options($indexTypes)->required();;
                    $form->show('select', 'action', '索引方法')->default('BTREE')->itemsTitleStyle('width:200px;text-align:center;')->options($indexAction)->required();
                    $form->show('textarea', 'comment', '注释')->itemsTitleStyle('width:400px;text-align:center;');
                })->required();
            $form->render();
        }
    }

    /**
     * 检索数据表写到表里面
     * @Author   July
     * @DateTime 2023-05-27T12:28:29+0800
     * @param    boolean  $isReturnResult  是否直接返回结果到浏览器，true，false
     */
    public function updateTables($isReturnResult = true)
    {
        $list = Db::query("SHOW TABLE STATUS");
        if (!$list) {
            $this->dataModel->where('1=1')->delete();
        } else {
            $aloow_field = ['name', 'engine', 'rows', 'data_length', 'max_data_length', 'index_length', 'data_free', 'auto_increment', 'collation', 'comment', 'create_time', 'update_time'];
            $names       = [];
            foreach ($list as $table_k => $table_v) {
                $table = [];
                foreach ($table_v as $field_name => $field) {
                    $field_name = strtolower($field_name);
                    if (in_array($field_name, $aloow_field)) {
                        $table[$field_name] = $field;
                    }
                }
                $sql = Db::query('show create table ' . $table['name']);
                if ($sql) {
                    $sql = $sql[0]['Create Table'];
                }
                $preg = '/CHARSET=([a-zA-Z\-\d]+)/';
                preg_match_all($preg, $sql, $res);
                if (isset($res[1][0])) {
                    $table['charset'] = $res[1][0];
                }
                $names[] = $table['name'];
                if ($table['auto_increment'] === null) {
                    $table['auto_increment'] = 0;
                }
                $find = $this->dataModel->where(['name' => $table['name']])->find();
                if ($find) {
                    $this->dataModel->where(['name' => $table['name']])->update($table);
                } else {
                    $this->dataModel->insert($table);
                }
            }
        }

        $list = $this->dataModel->field('id,name')->select()->toArray();
        foreach ($list as $k => $v) {
            if (!in_array($v['name'], $names)) {
                $this->dataModel->where(['id' => $v['id']])->delete();
            }
        }
        if ($isReturnResult) {
            $this->success('操作成功');
        }

    }

    /**
     * 更新字段
     * @Author   July
     * @DateTime 2023-07-16T19:57:19+0800
     * @param    array                   $find            当前数据表在database_manage表中的数据
     * @param    array                   $data            当前数据表信息
     */
    private function updateField($find = [], $data = [])
    {
        $database = Config("database.connections.mysql.database");
        //拼凑创建数据表sql语句
        $sql         = [];
        $indexFields = [];
        foreach ($data['fields'] as $field_k => $field) {
            //上一个字段
            $first_field = '';
            if ($field_k == 0) {
                $first_field = 'FIRST';
            } else {
                $first_field = "AFTER `{$data['fields'][$field_k - 1]['field']}`";
            }
            $f              = (in_array($field['type'], DatabaseManageModel::ALLOW_USE_LENGTH_TYPES) && $field['length'] > 0 ? "({$field['length']}" . ($field['decimal'] > 0 ? "," . $field['decimal'] : "") . ")" : "");
            $original_field = isset($field['original_field']) && $field['original_field'] ? "`{$field['original_field']}`" : "";
            //判断是否是主键字段
            if (isset($field['key']) && in_array(1, $field['key'])) {
                $sql[]         = (isset($field['original_field']) && $field['original_field'] ? "CHANGE COLUMN " : "ADD COLUMN ") . "{$original_field} `{$field['field']}` {$field['type']} {$f} unsigned" . (isset($field['notnull']) && in_array('1', $field['notnull']) ? " NOT NULL" : "") . (isset($field['auto_increment']) && in_array(1, $field['auto_increment']) ? " AUTO_INCREMENT" : "") . ($field['default'] != "" ? " DEFAULT '{$field['default']}'" : "") . ($field['comment'] ? " COMMENT '{$field['comment']}'" : "") . " {$first_field}";
                $indexFields[] = "`{$field['field']}`";
            } else {
                $sql[] = (isset($field['original_field']) && $field['original_field'] ? "CHANGE COLUMN " : "ADD COLUMN ") . "{$original_field} `{$field['field']}` {$field['type']} {$f}" . (isset($field['notnull']) && in_array('1', $field['notnull']) ? " NOT NULL" : "") . ($field['default'] != "" ? " DEFAULT '{$field['default']}'" : "") . ($field['comment'] ? " COMMENT '{$field['comment']}'" : "") . " {$first_field}";
            }
        }

        //将更新字段的语句创建
        //删除主键
        $sql[] = "DROP PRIMARY KEY";
        //如果有主键字段
        if ($indexFields) {
            $sql[] = "ADD PRIMARY KEY (" . implode(", ", $indexFields) . ") USING BTREE";
        }
        //更新引擎
        $sql[] = "ENGINE = {$data['engine']}";
        //设置表注释
        if ($data['comment']) {
            $sql[] = "COMMENT='{$data['comment']}'";
        }
        $sql = "ALTER TABLE `{$database}`.`{$find['name']}` " . implode(',', $sql);
        // echo $sql;exit;
        Db::execute($sql);
    }

    /**
     * 模型关联
     * @Author
     * @DateTime 2023-07-16T16:32:01+0800
     */
    public function modelRelation()
    {
        $pk        = input("get." . $this->getPk());
        $database  = Config("database.connections.mysql.database");
        $tableName = $this->dataModel->where([$this->getPk() => $pk])->value('name');

        if (request()->isPost()) {
            $models = input('post.models/a', []);
            if (!$models) {
                model\ModelRelation::where(['table_name' => $tableName])->delete();
                return json(['code' => 1, 'msg' => '保存成功！']);
            }
            $list = (new model\ModelRelation())->where(['table_name' => $tableName])->select()->toArray();
            foreach ($list as $lk => $lv) {
                $is_delete = true;
                foreach ($models as $mk => $mv) {
                    if ($lv['field'] == $mv['field']) {
                        $is_delete = false;
                    }
                }
                if ($is_delete) {
                    model\ModelRelation::where(['table_name' => $tableName, 'field' => $lv['field']])->delete();
                }
            }

            foreach ($models as $mk => $mv) {
                $mv['table_name'] = $tableName;
                try {
                    $info = [
                        'field|字段'             => 'require',
                        'relation_table|关联表'   => 'require',
                        'relation_field|关联字段'  => 'require',
                        'relation_action|关联方法' => 'require',
                        'relation_type|关联类型'   => 'require',
                        'relation_name|关联名称'   => 'require',
                    ];
                    validate($info)->check($mv);
                } catch (\think\exception\ValidateException $e) {
                    $this->error($e->getError());
                }

                $id = '';
                if (isset($mv['id'])) {
                    $id = $mv['id'];
                    unset($mv['id']);
                }
                $find = (new model\ModelRelation())->where(['id' => $id])->find();
                if ($find) {
                    $result = $find->save($mv);
                } else {
                    $result = (new model\ModelRelation())->save($mv);
                }
                if (!$result) {
                    return json(['code' => 0, 'msg' => '保存失败！']);
                }
            }
            return json(['code' => 1, 'msg' => '保存成功！']);
        } else {
            $builder = $this->builder();
            $form    = new \july\julyadmin\builder\Form($builder, '模型关联' . $tableName);
            //设置表单整体宽度为最大12
            $form->setColWidth(12);
            //设置标题宽度为2，设置表单元素宽度为10
            $form->size(2, 10);
            $form->pk   = $this->getPk();
            $items      = model\ModelRelation::where(['table_name' => $tableName])->select()->toArray();
            $form->data = ['models' => $items];

            //获取所有数据表列表
            $table_list = Db::query("SHOW TABLE STATUS");
            $tables     = [];
            foreach ($table_list as $k => $v) {
                $tables[$v['Name']] = $v['Name'];
            }

            //获取当前数据表所有字段
            $fields      = [];
            $fields_list = Db::query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{$database}' AND TABLE_NAME='{$tableName}' ORDER BY ORDINAL_POSITION ASC");
            foreach ($fields_list as $k => $v) {
                $fields[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }

            $form->show('items', 'models', '模型关联')->showRowHandleMenus(true)->addBtn(true)->deleteBtn(true)->style('min-width:1100px;')
                ->with(function ($form) use ($tables, $fields) {
                    $form->show('hidden', 'id', 'ID');
                    $form->show('select', 'field', '字段')->options($fields)->itemsTitleStyle('width:200px;text-align:center;')->required();
                    $form->show('select', 'relation_table', '关联表/关联字段')->itemsTitleStyle('width:600px;text-align:center;')->options($tables)
                        ->withNext(function ($form) {
                            $form->show('select', 'relation_field', '关联字段')->rows(1000)->setParentId('relation_table')->itemsTitleStyle('width:200px;text-align:center;')->dataUrl(url('DatabaseManage/tableFieldsSelectPage'))->required();
                        })->required();
                    $form->show('select', 'relation_action', '关联方法')->itemsTitleStyle('width:200px;text-align:center;')->options(['belongsTo' => 'belongsTo', 'hasOne' => 'hasOne', 'hasMany' => 'hasMany'])->required();
                    $form->show('select', 'relation_type', '关联类型')->itemsTitleStyle('width:200px;text-align:center;')->options(['with' => 'with', 'inner' => 'inner', 'left' => 'left', 'right' => 'right'])->required();
                    $form->show('text', 'relation_name', '关联名称')->itemsTitleStyle('width:200px;text-align:center;')->required();
                    $form->show('textarea', 'comment', '注释')->itemsTitleStyle('width:400px;text-align:center;');
                })->help('1.只有当关联类型为with的时候，关联方法才有实际意义，否则都是生成一条sql语句进行查询。<br>
                2.当需要关联查询的表数据很庞大的时候，建议使用with预查询，可以提高效率。<br>
                3.当代码和数据库不在同一个地方的时候，用with查询就会很慢，如果需要关联查询的数据表数据不多，比如是分类，管理员的时候，建议使用inner，left，right，根据实际情况使用，会生成一条sql语句查询，提高效率，当然如果数据量很庞大的时候，这个会更慢，建议依旧用with！')->required();
            $form->render();
        }
    }

    /**
     * 获取指定数据表下拉列表
     * @Author   July
     * @DateTime 2023-07-23T00:18:15+0800
     * @param    string                   $parent_id 数据表名称
     * @return   array                                字段列表
     */
    public function tableFieldsSelectPage($parent_id = '')
    {
        $database = Config("database.connections.mysql.database");
        $list     = Db::query("SELECT COLUMN_NAME AS id,COLUMN_NAME AS text FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{$database}' AND TABLE_NAME='{$parent_id}' ORDER BY ORDINAL_POSITION ASC");
        return json(["code" => 1, "total" => count($list), "data" => $list]);
    }

    /**
     * 清理索引
     * @Author   July
     * @DateTime 2023-07-23T09:47:40+0800
     */
    public function clearData()
    {
        $list = Db::query("SHOW TABLE STATUS");
        foreach ($list as $table_k => $table_v) {
            Db::execute("OPTIMIZE TABLE {$table_v['Name']}");
        }
        $this->updateTables(false);
        $this->success("操作成功");
    }

    /**
     * 创建模型
     * @Author   July
     * @DateTime 2024-01-13T19:56:47+0800
     */
    public function createModel()
    {
        $pk       = input("get." . $this->getPk());
        $database = Config("database.connections.mysql.database");
        $find     = $this->dataModel->where([$this->getPk() => $pk])->find();

        if (request()->isPost()) {
            $data = input('post.', []);
            try {
                $info = [
                    'model_position|模型位置' => 'require',
                ];
                validate($info)->check($data);
            } catch (\think\exception\ValidateException $e) {
                $this->error($e->getError());
            }

            $path = '../' . $data['model_position'];
            //创建目录
            if (!is_readable($path)) {
                is_file($path) or mkdir($path, 0777, true);
                chmod($path, 0777);
            }
            //软删除引用
            $use_soft_delete = isset($data['delete_time_field']) && $data['delete_time_field'] ? 'use think\model\concern\SoftDelete;' : '';
            //配置
            $configs = $this->handleModelConfigs($data);
            //关联信息
            $relations = $this->handleModelRelations($database, $find['name']);
            //模型名称
            $model_name = $this->camelize(substr_replace($find['name'], '', 0, strlen(Config('database.connections.mysql.prefix'))));
            //命名空间
            $namespace = str_replace('/', '\\', $data['model_position']);
            $template  = <<<EOT
<?php
{$data['model_desc']}
namespace {$namespace};

use think\Model;
{$use_soft_delete}

class {$model_name} extends Model
{
{$configs}
{$relations}
}
EOT;
            //保存文件名
            $filename = $path . '/' . $model_name . '.php';
            if (!isset($data['is_cover_model']) || !$data['is_cover_model']) {
                if (file_exists($filename)) {
                    $this->error('模型文件已存在');
                }
            } else {
                if (file_exists($filename)) {
                    unlink($filename);
                }
            }
            file_put_contents($filename, $template);
            $this->success('创建成功', '');
        } else {
            $builder = $this->builder();
            $form    = new \july\julyadmin\builder\Form($builder, '生成模型' . $find['name']);
            //设置表单整体宽度为最大12
            $form->setColWidth(12);
            //设置标题宽度为2，设置表单元素宽度为10
            $form->size(2, 10);
            $form->pk               = $this->getPk();
            $data                   = [];
            $data['model_position'] = 'app/common/model';
            $data['model_name']     = $this->camelize(substr_replace($find['name'], '', 0, strlen(Config('database.connections.mysql.prefix')))) . '.php';
            $create_time            = date('Y-m-d H:i:s');
            $data['model_desc']     = <<<EOT

// +----------------------------------------------------------------------
// | Author:
// +----------------------------------------------------------------------
// | 创建时间: {$create_time}
// +----------------------------------------------------------------------
// | {$find['comment']}
// +----------------------------------------------------------------------

EOT;
            //获取当前数据表所有字段
            $fields      = [];
            $fields_list = Db::query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{$database}' AND TABLE_NAME='{$find['name']}' ORDER BY ORDINAL_POSITION ASC");
            foreach ($fields_list as $k => $v) {
                $fields[$v['COLUMN_NAME']] = $v['COLUMN_NAME'];
            }

            $form->data = $data;
            $types      = DatabaseManageModel::TYPES;
            $form->show('text', 'model_position', '模型位置')->required()->help('不可乱填，同时也会是模型的命名空间，必须按照ThinkPHP框架要求填写。');
            $form->show('html', 'model_name', '模型名称');
            $form->show('textarea', 'model_desc', '模型注释')->rows(10)->maxlength(10000);
            $form->show('select', 'keyfield', '主键字段')->options($fields)->default('id')->required();
            $form->show('select', 'create_time_field', '新增时间字段')->options($fields)->default('create_time')->help('不选择则创建时间自动时间不生效！');
            $form->show('select', 'update_time_field', '更新时间字段')->options($fields)->default('update_time')->help('不选择则更新时间自动时间不生效！');
            $form->show('select', 'delete_time_field', '软删除字段')->options($fields)->default('delete_time')->help('不选择则软删除不生效！');
            $form->show('select', 'is_cover_model', '是否覆盖模型')->options([0 => '不覆盖', 1 => '覆盖'])->default('0');
            $form->render();
        }
    }

    /**
     * 处理模型配置信息
     * @Author
     * @DateTime 2023-08-13T12:23:36+0800
     * @param    array                   $data 表单数据
     * @return   string                  配置信息
     */
    private function handleModelConfigs($data = [])
    {
        $configs = '';
        if (isset($data['delete_time_field']) && $data['delete_time_field']) {
            $configs .= "
    use SoftDelete;";
        }
        $configs .= "
    //主键
    protected \$pk = '{$data['keyfield']}';";

        if ((isset($data['create_time_field']) && $data['create_time_field']) || (isset($data['update_time_field']) && $data['update_time_field']) || (isset($data['delete_time_field']) && $data['delete_time_field'])) {
            $configs .= "
    //设置自动时间写入类型
    protected \$autoWriteTimestamp = 'datetime';";
        } else {
            $configs .= "
    //设置自动时间写入类型
    protected \$autoWriteTimestamp = false;";
        }

        if (isset($data['create_time_field']) && $data['create_time_field']) {
            $configs .= "
    //设置创建时间字段
    protected \$createTime = '{$data['create_time_field']}';";
        }

        if (isset($data['update_time_field']) && $data['update_time_field']) {
            $configs .= "
    //设置更新时间字段
    protected \$updateTime = '{$data['update_time_field']}';";
        }

        if (isset($data['delete_time_field']) && $data['delete_time_field']) {
            $configs .= "
    //设置删除时间字段
    protected \$deleteTime = '{$data['delete_time_field']}';";
        }
        return $configs;
    }

    /**
     * 处理模型关联信息
     * @Author
     * @DateTime 2023-08-13T12:26:08+0800
     * @param    string                   $database 数据库名
     * @param    string                   $tableName 表名
     * @return   string                   关联信息
     */
    private function handleModelRelations($database = '', $tableName = '')
    {
        $DateTime  = date("Y-m-d H:i:s");
        $list      = model\ModelRelation::where(['table_name' => $tableName])->select()->toArray();
        $relations = '';
        foreach ($list as $rk => $rv) {
            $relation_model_class = $this->camelize(substr_replace($rv['relation_table'], '', 0, strlen(Config('database.connections.mysql.prefix'))));
            if ($rv['comment']) {
                $rv['comment'] = "
    /**
     * {$rv['comment']}
     * @Author
     * @DateTime {$DateTime}
     */";
            }
            if ($rv['relation_action'] == 'belongsTo') {
                $relations .= "
    {$rv['comment']}
    public function {$rv['relation_name']}()
    {
        return \$this->belongsTo({$relation_model_class}::class, '{$rv['field']}', '{$rv['relation_field']}');
    }

    ";
            }

            if ($rv['relation_action'] == 'hasOne') {
                $relations .= "
    {$rv['comment']}
    public function {$rv['relation_name']}()
    {
        return \$this->hasOne({$relation_model_class}::class, '{$rv['relation_field']}', '{$rv['field']}');
    }

    ";
            }

            if ($rv['relation_action'] == 'hasMany') {
                $relations .= "
    {$rv['comment']}
    public function {$rv['relation_name']}()
    {
        return $this->hasMany({$relation_model_class}::class, '{$rv['field']}', '{$rv['relation_field']}');
    }

    ";
            }
        }
        return $relations;
    }

    /**
     * 保存
     * @Author   July
     * @DateTime 2023-11-16T20:45:54+0800
     * @param  integer $isEdit 是否是编辑 0否，1是
     */
    protected function save($isEdit)
    {
        $pk = input("get." . $this->getPk());

        $data = request()->only([
            'name',
            'engine',
            'charset',
            'collation',
            'comment',
            'fields',
        ], 'post');

        try {
            $info = [
                'name|表名'        => 'require',
                'engine|引擎'      => 'require',
                'charset|编码'     => 'require',
                'collation|排序规则' => 'require',
                'fields|字段'      => 'require',
            ];
            validate($info)->check($data);
        } catch (\think\exception\ValidateException $e) {
            $this->error($e->getError());
        }

        foreach ($data['fields'] as $field_key => $field) {
            try {
                $info = [
                    'field|字段名' => 'require',
                    'type|字段类型' => 'require',
                ];
                validate($info)->check($field);
                //非下类型字段不能设置小数
                if (!in_array(strtolower($field['type']), ['decimal', 'numeric', 'double']) && $field['decimal'] !== '') {
                    $this->error('当前字段类型不能设置小数');
                }

                //以下字段不能设置长度
                if (!in_array(strtolower($field['type']), DatabaseManageModel::ALLOW_USE_LENGTH_TYPES) && $field['length'] !== '') {
                    $this->error('当前字段类型不能设置长度');
                }

                //以下类型字段，小数字段默认为0
                if (in_array(strtolower($field['type']), ['decimal', 'numeric', 'double']) && $field['decimal'] == '') {
                    $data['fields'][$field_key]['decimal'] = 0;
                }
            } catch (\think\exception\ValidateException $e) {
                $this->error($e->getError());
            }
        }

        $sort = array_column($data['fields'], 'sort'); //取出数组中type的一列，返回一维数组
        array_multisort($sort, SORT_ASC, $data['fields']); //排序，根据$type排序

        if (!$isEdit) {
            //拼凑创建数据表sql语句
            $sql    = [];
            $sql[]  = "CREATE TABLE If Not Exists `{$data['name']}` (";
            $fields = [];
            $indexs = [];
            foreach ($data['fields'] as $field_key => $field) {
                $f = (in_array($field['type'], DatabaseManageModel::ALLOW_USE_LENGTH_TYPES) && $field['length'] > 0 ? "({$field['length']}" . ($field['decimal'] > 0 ? "," . $field['decimal'] : "") . ")" : "");
                //判断是否是主键字段
                if (isset($field['key']) && in_array(1, $field['key'])) {
                    $fields[] = "`{$field['field']}` {$field['type']} {$f} unsigned" . (isset($field['notnull']) && in_array('1', $field['notnull']) ? " NOT NULL" : "") . (isset($field['auto_increment']) && in_array(1, $field['auto_increment']) ? " AUTO_INCREMENT" : "") . ($field['default'] != "" ? " DEFAULT '{$field['default']}'" : "") . ($field['comment'] ? " COMMENT '{$field['comment']}'" : "") . ",";
                    $indexs[] = " PRIMARY KEY (`id`),";
                } else {
                    $fields[] = "`{$field['field']}` {$field['type']} {$f}" . (isset($field['notnull']) && in_array('1', $field['notnull']) ? " NOT NULL" : "") . ($field['default'] != "" ? " DEFAULT '{$field['default']}'" : "") . ($field['comment'] ? " COMMENT '{$field['comment']}'" : "") . ",";
                }
            }
            $fields = array_merge($fields, $indexs);
            $sql[]  = trim(implode('', $fields), ',');
            $sql[]  = ")";
            $sql[]  = " ENGINE={$data['engine']}";
            $sql[]  = " AUTO_INCREMENT=1";
            $sql[]  = " DEFAULT";
            $sql[]  = " CHARSET={$data['charset']}";
            $sql[]  = " ROW_FORMAT=DYNAMIC";
            if ($data['comment']) {
                $sql[] = " COMMENT='{$data['comment']}'";
            }
            $sql = implode('', $sql);
            Db::execute($sql);
            $this->updateTables(false);
            $this->success('保存成功', '');
        }

        if ($isEdit) {
            $find = $this->dataModel->where([$this->getPk() => $pk])->find();
            if (!$find) {
                $this->error('数据表信息不存在');
            }
            $database = Config("database.connections.mysql.database");
            //当前表所有字段信息
            $columns = Db::query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='{$database}' AND TABLE_NAME='{$data['name']}'");
            //修改字段
            $this->updateField($find, $data);
            //删除已经删除掉的字段
            foreach ($columns as $ck => $cv) {
                $is_delete = true;
                foreach ($data['fields'] as $field_k => $field_v) {
                    if ($cv['COLUMN_NAME'] == $field_v['original_field']) {
                        $is_delete = false;
                    }
                }
                //把已经删除的字段从表结构删除掉
                if ($is_delete) {
                    $sql = "ALTER TABLE `{$database}`.`{$find['name']}` DROP COLUMN `{$cv['COLUMN_NAME']}`;";
                    Db::execute($sql);
                    continue;
                }
            }
            $sql = "ALTER TABLE `{$database}`.`{$find['name']}` CONVERT TO CHARACTER SET `{$data['charset']}` COLLATE `{$data['collation']}`;";
            Db::execute($sql);

            //重新更新表信息
            $this->updateTables(false);
            $this->success('保存成功', '');
        }
    }

}
